Rename Table

If your transformation type is Rename Table, you can do the following:

• Rename table to (string)
• Add a prefix or suffix
• Remove a prefix or suffix
• Replace a prefix or suffix with different characters
• Convert table name to uppercase
• Convert table name to lowercase
• Rename table (expression)

Rename table to (string)

Use the Rename table to: [string] option to change the name of all tables that you defined in the Transformation scope step to a different name. For example, if you have a table called EMPLOYEE and want to change all instances of this name to EMP then enter the string EMP. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the table name for all tables that fit the definition you created in the Transformation scope step. For example, if the table name is EMPLOYEES, you can add a suffix, such as TAR or _TAR to the table name for all tables with that table name. In this case, the resulting table name will be EMPLOYEESTAR or EMPLOYEES_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching table names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want as the prefix or suffix. If you want to include an underscore or other legal character to separate the prefix/suffix from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or end of a table name for all tables that fit the definition you created in the Transformation scope step.

For example, you can use this option to remove the letters _REV from the table name for all tables with the name EMPLOYEES. In this case the table name in the target will be EMPLOYEES.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> Insert Characters from matching table names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want to remove. If you want to remove an underscore or other legal character from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of characters. You determine whether to replace the characters at the beginning or end of a table name for all tables that fit the definition you created in the Transformation scope step.

For example, you can use this option to replace the letters _ORIG with _REPL in the table names for all tables called EMPLOYEE_ORIG. In this case the table name in the target will be EMPLOYEE_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click the first [string] to activate the field.
4. Type the characters from the existing (source) schema that you want to replace. If you want to include an underscore or other legal character from the original name in the string that you want to replace, you must add it as part of the character string.
5. Click the second [sting] to activate the field.
6. Type the characters you want to use in the target. These characters replace the original (source) characters in the target.
7. Click Finish to add the rule to the Global Rules list.

Convert table name to uppercase

Use the convert to uppercase option to convert a table name to all upper case. For example:

• Table_cat, becomes TABLE_CAT
• table_cat, becomes TABLE_CAT
• taBLe_Cat, becomes TABLE_CAT

To globally change the table name to all uppercase:

1. Select Convert table name to uppercase.
2. Click Finish to add the rule to the Global Rules list.

Convert table name to lowercase

Use the convert to lowercase option to convert a table name to all lower case. For example:

• Table_cat, becomes table_cat
• TABLE_CAT, becomes table_cat
• taBLe_Cat, becomes table_cat

To globally change the table name to all lowercase:

1. Select Convert table name to lowercase.
2. Click Finish to add the rule to the Global Rules list.

Rename table (expression)

Use the Rename table to [expression] option to change the name of all tables that fit the definition you created in the Transformation scope step. For example, if you have a table called EMPLOYEE and want to change all instances of this name as defined in the previous step it to EMP.

To change the table name:

1. Select Rename table to: [expression]

2. Click the button to the right of the Rename table option to open the Expression Editor. For information on how to use the Expression Editor, see Using the Expression Builder. Then go to step 4.

   or

   Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the table. For example:

   • "New_Table"
   • ’PREF_’||$TABLE_NAME_VAR||’_SUFF’

   You can use the following variables in the SQLite expression:

   • $SCHEMA_NAME_VAR
   • $TABLE_NAME_VAR
   • $COLUMN_NAME_VAR
   • $COLUMN_DATATYPE_VAR